---
title: Joined Tables
---

## Overview

Postgres [materialized views](https://www.postgresql.org/docs/current/rules-materializedviews.html) can be used to create a single index
over multiple tables. This is useful for joining and searching over normalized data.

## Basic Usage

In this example, we will create two tables: `mock_products` and `mock_reviews`.

```sql
CREATE TABLE mock_products (
    id SERIAL PRIMARY KEY,
    product_name TEXT
);

INSERT INTO mock_products (product_name)
VALUES ('Flat Screen TV'), ('MP3 Player');

SELECT * FROM mock_products;
```

<Accordion title="Expected Response">
```csv
 id |  product_name
----+----------------
  1 | Flat Screen TV
  2 | MP3 Player
(2 rows)
```
</Accordion>

```sql
CREATE TABLE mock_reviews (
    review_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES mock_products(id),
    review TEXT
);

INSERT INTO mock_reviews (product_id, review)
VALUES (1, 'Amazing resolution'), (2, 'Amazing sound'), (2, 'Would recommend');

SELECT * FROM mock_reviews;
```

<Accordion title="Expected Response">
```csv
 review_id | product_id |       review
-----------+------------+--------------------
         1 |          1 | Amazing resolution
         2 |          2 | Amazing sound
         3 |          2 | Would recommend
(3 rows)
```
</Accordion>

The materialized view `product_reviews` joins data from both tables and produces a single view of product names and their
corresponding reviews.

```sql
CREATE MATERIALIZED VIEW product_reviews
AS SELECT r.review_id, p.product_name, r.review
FROM mock_reviews r
LEFT JOIN mock_products p ON p.id = r.product_id;

SELECT * FROM product_reviews;
```

<Accordion title="Expected Response">
```csv
 review_id |  product_name  |       review
-----------+----------------+--------------------
         1 | Flat Screen TV | Amazing resolution
         2 | MP3 Player     | Amazing sound
         3 | MP3 Player     | Would recommend
(3 rows)
```
</Accordion>

To search over `product_reviews`, it must first be indexed.

```sql
CALL paradedb.create_bm25(
  index_name => 'product_reviews',
  table_name => 'product_reviews',
  key_field => 'review_id',
  text_fields => paradedb.field('review') || paradedb.field('product_name')
);
```

Now, the materialized view can be searched:

```sql
SELECT * FROM product_reviews.search('review:amazing OR product_name:tv');
```

<Accordion title="Expected Response">
```csv
 review_id |  product_name  |       review
-----------+----------------+--------------------
         1 | Flat Screen TV | Amazing resolution
         2 | MP3 Player     | Amazing sound
(2 rows)
```
</Accordion>

## Materialized View Refresh

One caveat of Postgres materialized views is that they do not update in real time. Any inserts/updates/deletes to the underlying
joined tables will not be reflected in a materialized view until the view is refreshed.

```sql
REFRESH MATERIALIZED VIEW product_reviews;
```

Refreshing a materialized view reconstructs both the materialized view and BM25 index, which may take some time for large views.
There are several strategies that can help reduce the size of a materialized view. For instance, if there is a one-to-many mapping
between two tables, `array_agg` can be used to group related rows into arrays.

```sql
CREATE MATERIALIZED VIEW product_reviews_agg
AS SELECT p.id, p.product_name, array_agg(r.review) AS reviews
FROM mock_reviews r
LEFT JOIN mock_products p
ON p.id = r.product_id
GROUP BY p.product_name, p.id;

SELECT * FROM product_reviews_agg;
```

<Accordion title="Expected Response">
```csv
 id |  product_name  |               reviews
----+----------------+-------------------------------------
  1 | Flat Screen TV | {"Amazing resolution"}
  2 | MP3 Player     | {"Amazing sound","Would recommend"}
(2 rows)
```
</Accordion>

Arrays can be indexed and searched over in the same way as non-array types.

```sql
CALL paradedb.create_bm25(
  index_name => 'product_reviews_agg',
  table_name => 'product_reviews_agg',
  key_field => 'id',
  text_fields => paradedb.field('reviews') || paradedb.field('product_name')
);

SELECT * FROM product_reviews_agg.search('reviews:sound');
```

<Accordion title="Expected Response">
```csv
 id | product_name |               reviews
----+--------------+-------------------------------------
  2 | MP3 Player   | {"Amazing sound","Would recommend"}
(1 row)
```
</Accordion>

## Incremental Materialized Views

Incremental materialized views automatically stay up to date with the underlying data in real time.
While Postgres itself does not support incremental materialized views, we recommend trying [pg_ivm](https://github.com/sraoss/pg_ivm),
which adds support for incremental materialized views. It is important to note the limitations of `pg_ivm`, which include
lack of support for outer joins, partitioned tables, and certain aggregates.

## For Further Assistance

Improving search over joined data is an active area of development for ParadeDB. If the above strategies do not satisfy
your use case, please [open a Github issue](https://github.com/paradedb/paradedb/issues) or [contact support](mailto:support@paradedb.com) and
we'll be happy to discuss alternative solutions.
